from openpyxl import load_workbook
import os
import numpy as np
from datetime import datetime,timedelta,time
from tkinter import *
from tkinter.ttk import *
import timeit
import random
def do_file(start_time):
    folder_path = 'd:/data/'
    def process_worksheet(worksheet,start_time):
        num_x=max(int(worksheet.cell(ro, 3).value) for ro in range(worksheet.max_row-32, worksheet.max_row))
        end_time = timeit.default_timer()
        print(end_time - start_time,4)
        for row in range(worksheet.max_row+2, worksheet.max_row-10, -1):
            if worksheet.cell(row, 1).value is not None and worksheet.cell(row, 2).value is not None:
                prev_day = worksheet.cell(row, 1).value
                prev_time = worksheet.cell(row, 2).value
                new_day = prev_day + timedelta(days=1) if prev_time.hour == 20 else prev_day
                new_time =time(prev_time.hour+4,0) if prev_time.hour != 20 else time(0,0)
                row+=1
                end_time = timeit.default_timer()
                print(end_time - start_time,5)
                info_list=[]
                for column in range(4, worksheet.max_column + 1):
                    if worksheet.cell(row - 1, column).value is not None:
                        data=[]
                        for j in range(1,33):
                            data.append(worksheet.cell(row - j, column).value)
                        mean = sum(data) / len(data)
                        variance = sum((x - mean) ** 2 for x in data) / len(data)
                        std_dev = variance ** 0.5
                        info_list.append([column,mean,std_dev])
                end_time = timeit.default_timer()
                print(end_time - start_time,6)
                for i in range(num_x):
                    worksheet.cell(row + i, 1).value = new_day
                    worksheet.cell(row + i, 2).value = new_time
                    worksheet.cell(row + i, 3).value = i+1
                    for column in range(4, worksheet.max_column + 1):
                        if worksheet.cell(row - 1, column).value is not None:
                            mean,std_dev=info_list[column-4][1],info_list[column-4][2]
                            if np.random.normal(mean, std_dev)>=worksheet.cell(2,column).value:
                                worksheet.cell(row + i, column).value=round(worksheet.cell(2,column).value-random.uniform(0.002,0.008),3)
                            elif np.random.normal(mean, std_dev)<=worksheet.cell(3,column).value:
                                worksheet.cell(row + i, column).value=round(worksheet.cell(3,column).value+random.uniform(0.002,0.008),3)
                            else:
                                worksheet.cell(row + i, column).value = round(np.random.normal(mean, std_dev), 3)
                end_time = timeit.default_timer()
                print(end_time - start_time,7)
                break  
    for root, dirs, files in os.walk(folder_path):
        end_time = timeit.default_timer()
        print(end_time - start_time,1)
        for filename in files:
            if '.xlsx' in filename:
                file_path = os.path.join(root, filename)
                end_time = timeit.default_timer()
                print(end_time - start_time,2)
                workbook = load_workbook(file_path)
                worksheet = workbook.active
                end_time = timeit.default_timer()
                print(end_time - start_time,3)
                process_worksheet(worksheet,start_time)
                end_time = timeit.default_timer()
                print(end_time - start_time,8)
                workbook.save(file_path)
                end_time = timeit.default_timer()
                print(end_time - start_time,9)
                workbook.close()
                end_time = timeit.default_timer()
                print(end_time - start_time,10)
app = Tk()
app.title('TE数据处理 v1.0')
app.geometry('300x100+300+200')
app.configure(bg='lightBlue')
style = Style()  # 更改变量名为style，更具描述性
style.configure('.', background='lightBlue', foreground='darkGreen')
Label(text='请放置数据于D盘data文件夹内').place(x=70, y=10)
info_label = Label(text="info:")  
info_label.place(x=50, y=70)
# 更改函数名和参数，减少全局变量的使用
def process_file_wrapper(info_label):
    start_time = timeit.default_timer()
    do_file(start_time)
    end_time = timeit.default_timer()
    print(end_time - start_time)
    now = datetime.now()
    str="Done"+now.strftime("%Y-%m-%d %H:%M:%S")
    info_label.config(text=str)
# 更新按钮的command属性，使用新的函数和参数
button_once = Button(text='一次', command=lambda: process_file_wrapper(info_label))
button_once.place(x=80, y=40)
app.mainloop()